package com.hygj.excel;

import com.hygj.util.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author suzhen
 * @date 2017/4/12
 */
public abstract class BaseExcelUtil {
    protected final static Logger LOGGER = LoggerFactory.getLogger(BaseExcelUtil.class);

    private Workbook workbook = null;
    //用来比较用户ex，是不是高版本的。高版本的后缀
    private String xlsx = "xlsx";
    /**
     * 附件根目录
     */
    private String rootDir;

    private Font font;
    /**
     * 导入导出专用路径
     */
    private String excelPath;
    /**
     * 禁止赋值的字段，导入，需要过滤字段
     */
    private List<String> denyFields = new ArrayList<>();

    {
        denyFields.add("serialVersionUID");
        denyFields.add("id");
    }

    /**
     * 导入的数据行数
     */
    private int importCount = 0;
    /**
     * 储存循环读取数据的字段
     * 对面数据对象中的字段名字
     * 用户循环读取数据中的key的列表，并对应名字
     * map key => value 相当于pojo字段名字，value=》相当于 解释
     * 解析 根据field吧value
     */
    private Map<String, String> valueMapFields = new LinkedHashMap<>();
    /**
     * 包含顶部显示文字和每一行的数据读取字段名称
     * excel顶部标题部分
     * 可以是多行，这里包含上面的数据,基本添加上面的数据就可以，这个数据一般不用管，除非你的顶部超出2行
     */
    private List<Map<String, String>> headFields = new ArrayList<>();

    /**
     * 数据输出起始行
     */
    private int rowStart = 0;


    public void setRootDir(String rootDir) {
        this.rootDir = rootDir;
    }

    public String getRootDir() {
        if (StringUtils.isBlank(rootDir)) {
            rootDir = FileUtils.getWebRoot();
        }
        return rootDir;
    }

    public List<String> getDenyFields() {
        return denyFields;
    }

    public void setValueMapFields(Map<String, String> valueMapFields) {
        this.valueMapFields = valueMapFields;
        this.headFields.add(valueMapFields);
    }

    public Map<String, String> getValueMapFields() {
        return valueMapFields;
    }


    public int getImportCount() {
        return importCount;
    }

    public List<Map<String, String>> getHeadFields() {
        return headFields;
    }

    public Font getFont() {
        if (font == null) {
            font = this.workbook.createFont();
        }
        return font;
    }

    public int getRowStart() {
        if (rowStart == 0) {
            rowStart = getHeadFields().size();
        }
        return rowStart;
    }

    private BaseExcelUtil() {
    }

    /**
     * 导出
     *
     * @param rootDir 附件系统根目录
     * @param outPath 相对路径
     */
    public BaseExcelUtil(String rootDir, String outPath) {
        this.rootDir = rootDir;
        this.excelPath = outPath;

        if (outPath.endsWith(xlsx)) {
            this.workbook = new XSSFWorkbook();
        } else {
            this.workbook = new HSSFWorkbook();
        }
    }

    /**
     * 导入初始化
     * 文件夹是绝对路径,文件全路径
     *
     * @param inPath
     */
    public BaseExcelUtil(String inPath) throws IOException {
        this.excelPath = inPath;
        File file2 = new File(excelPath);
        //exists 判断是否存在
        if (!file2.exists()) {
            LOGGER.error("文件导入文件地址不存在:{}" ,excelPath);
            throw new FileNotFoundException(excelPath + "文件不存在");
        }
        //文件输入流 处理
        InputStream in = new FileInputStream(file2);
        if (excelPath.endsWith(xlsx)) {
            this.workbook = new XSSFWorkbook(in);
        } else {
            //低版本的实例化方法
            this.workbook = new HSSFWorkbook(in);
        }
    }

    /**
     * 数据转换
     *
     * @param a
     * @return
     */
    public double getResult(float a) {
        BigDecimal bigDecimal = new BigDecimal(a);
        BigDecimal b = bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP);
        return b.floatValue() * 100;
    }

    public double getResult(double a) {
        BigDecimal bigDecimal = new BigDecimal(a);
        BigDecimal b = bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP);
        return b.doubleValue() * 100;
    }


    /**
     * 表达式查找字符串是否存在
     *
     * @param str
     * @param reg
     * @return
     */
    public boolean findString(String str, String reg) {
        Pattern pattern = Pattern.compile(reg);
        Matcher matcher = pattern.matcher(str);
        return matcher.find();
    }

    /**
     * @param a
     * @return
     * 把 Object 转 整形
     * BigDecimal 高精准处理数字
     */
    protected int getMapDate(Object a) {
        int result = 0;
        if (a == null) {
            return result;
        }
        if (a.getClass() == BigDecimal.class) {
            BigDecimal bigDecimal = (BigDecimal) a;
            result = bigDecimal.intValue();
        } else if (a.getClass() == Long.class) {
            long view = (long) a;
            result = Long.valueOf(view).intValue();
        } else {
            result = (int) a;
        }
        return result;
    }

    /**
     * 设置导出的头部
     *
     * @param sheet
     */
    public void setSheetHeader(Sheet sheet) {

        // 循环所有的
        int r = 0;
        for (Map<String, String> map : getHeadFields()) {
            int c = 0;
            //poi   Row是里的 一行
            Row row = sheet.createRow(r);
            // 循环标题
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if (entry.getKey().startsWith(":")) {
                    String key = entry.getKey().substring(1);
                    String[] keys = key.split("-");
                    if (keys.length == 2) {
                        // 合并，如果是有一行数据表示合并一行
                        mergeCell(sheet, r, r, Integer.valueOf(keys[0]), Integer.valueOf(keys[1]));
                        createCell(row, Integer.valueOf(keys[0]), entry.getValue());
                    } else if (keys.length == 4) {
                        // 合并，这里处室上下左右合并
                        // 顺序是左-右-上-下
                        mergeCell(sheet, r, Integer.valueOf(keys[0]), Integer.valueOf(keys[1]), Integer.valueOf(keys[2]));
                        createCell(row, Integer.valueOf(keys[0]), entry.getValue());
                    }

                } else {
                    createCell(row, c, entry.getValue());
                }
                c++;
            }
            r++;
        }
    }

    /**
     * 自定义设置莫一行的
     *
     * @param sheet
     * @param headers
     * @param rowCount
     */
    public void setSheetHeaderCustom(Sheet sheet, Map<String, String> headers, int rowCount) {
        short i = 0;
        Row row = sheet.createRow(rowCount);
        for (Map.Entry<String, String> entry : headers.entrySet()) {
            createCell(row, i, entry.getValue());
            i++;
        }
    }

    /**
     * 数据填充
     *
     * @param row
     * @param column
     * @param value
     * @param hAlign
     * @param vAlign
     */
    void createCell(Row row, int column, Object value, HorizontalAlignment hAlign, VerticalAlignment vAlign) {
        Cell cell = row.createCell(column);
        if (value == null) {
            return;
        }
        //设置样式
        CellStyle cellStyle = workbook.createCellStyle();
        //设置文本自动换行，是否应该包装
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(hAlign);
        cellStyle.setVerticalAlignment(vAlign);
        cell.setCellStyle(cellStyle);

        if (value.getClass().equals(Integer.class) || value.getClass().equals(int.class)) {
            int val = (int) value;
            cell.setCellValue(val);
        } else if (value.getClass().equals(String.class)) {
            String val = (String) value;
            cell.setCellValue(val);
        } else if (value.getClass().equals(Long.class) || value.getClass().equals(long.class)) {
            long val = (long) value;
            cell.setCellValue(val);
        } else if (value.getClass().equals(Float.class) || value.getClass().equals(float.class)) {
            float val = (float) value;
            cell.setCellValue(val);
        } else if (value.getClass().equals(Double.class) || value.getClass().equals(double.class)) {
            double val = (double) value;
            cell.setCellValue(val);
        } else if (value.getClass().equals(Date.class)) {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            cell.setCellValue(dateFormat.format(value));
        }
    }

    void createCell(Row row, int column, Object value) {
        createCell(row, column, value, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
    }


    /**
     * 合并cell
     *
     * @param sheet
     * @param a     first row
     * @param b     last row
     * @param c     first column
     * @param d     last column
     */
    private void mergeCell(Sheet sheet, int a, int b, int c, int d) {
        sheet.addMergedRegion(new CellRangeAddress(a, b, c, d));
    }

    /**
     * 把对象转 map类型导出
     *
     * @param subjects
     * @throws IOException
     */
    public void outWithMap(List<Map<String, Object>> subjects) throws IOException {

        Sheet sheet = workbook.createSheet("数据导出");
        // 设置导出的头部
        setSheetHeader(sheet);
        try {
            // 导出的内容
            if (subjects != null && subjects.size() > 0) {
                int j = getRowStart();
                // 序号
                int number = 1;
                for (Map<String, Object> subject : subjects) {
                    int i = 0;
                    Row row = sheet.createRow(j);
                    for (Map.Entry<String, String> entry : getValueMapFields().entrySet()) {
                        switch (entry.getKey()) {
                            // 序号设置
                            case "id":
                                createCell(row, i, number);
                                break;
                            default:
                                createCell(row, i, subject.get(entry.getKey()));
                                break;
                        }
                        i++;
                    }
                    number++;
                    j++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 吧数据写入 workbook
     *
     * @param objects
     * @throws IOException
     */
    public void outWithObject(List<Object> objects) throws IOException {

        Sheet sheet = workbook.createSheet("数据导出");
        // 设置导出的头部
        setSheetHeader(sheet);
        try {
            // 导出的内容
            if (objects != null && objects.size() > 0) {
                int j = getRowStart();
                // 序号
                int number = 1;
                for (Object o : objects) {
                    int i = 0;
                    Row row = sheet.createRow(j);
                    for (Map.Entry<String, String> entry : getValueMapFields().entrySet()) {
                        switch (entry.getKey()) {
                            // 序号设置
                            case "id":
                                createCell(row, i, number);
                                break;
                            default:
                                //createCell(row, i, PojoParseFieldUtil.getFiledValue(o, entry.getKey()));
                                break;
                        }
                        i++;
                    }
                    number++;
                    j++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 输出到文件
     *
     * @throws IOException
     */
    public void out() throws IOException {
        String fileExportPath = getRootDir() + excelPath;
        LOGGER.debug(fileExportPath);
        File file = new File(fileExportPath);
        File parent = file.getParentFile();
        if (!parent.isDirectory()) {
            Boolean b = parent.mkdirs();
        }
        FileOutputStream out = new FileOutputStream(fileExportPath);
        workbook.write(out);
        workbook.close();
        out.close();
    }

    /**
     * 文件路径生成规则
     *
     * @param name
     * @return
     */
    public String getExportPath(String name) {
        return "" + name;
    }

    /**
     * 设置字段的值
     *
     * @param objectSource
     * @param field
     * @param cell
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    private void setFieldValue(Object objectSource, Field field, Cell cell)
            throws NoSuchFieldException, IllegalAccessException {
        if (objectSource == null || field == null || cell == null) {
            return;
        }
        field.setAccessible(true);
        Type type = field.getType();

        //处理字符串
        if (type.equals(String.class)) {
            if (cell.getCellTypeEnum() == CellType.STRING) {
                field.set(objectSource, cell.getStringCellValue().trim());
            } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                field.set(objectSource, String.valueOf(cell.getNumericCellValue()).trim());
            }
        } else if (type.equals(Integer.class) || type.equals(int.class)) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                field.setInt(objectSource, Double.valueOf(cell.getNumericCellValue()).intValue());
            }
        } else if (type.equals(Long.class) || type.equals(long.class)) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                field.setLong(objectSource, Double.valueOf(cell.getNumericCellValue()).longValue());
            }
        } else if (type.equals(Double.class) || type.equals(double.class)) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                field.setDouble(objectSource, cell.getNumericCellValue());
            }
        } else if (type.equals(Date.class)) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    field.set(objectSource, HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    //也可以直接读取日期
                }
            }
        }
    }


    /**
     * 不能传入内部类
     * 根据类的反射导入数据，
     * 要求excel排列对顺必须和类字段位置顺序一致
     * 根据cell值得位置和field的位置绑定
     *
     * @param clazz
     * @param limit
     * @throws IOException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchFieldException
     */
    public <T> List<T> importWithClass(Class<T> clazz, int limit)
            throws IOException, IllegalAccessException, InstantiationException, NoSuchFieldException {


        List<T> list = new ArrayList<>();
        int sheetsCount = workbook.getNumberOfSheets();
        Class fType;
        T t;
        for (int i = 0; i < sheetsCount; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                // 过滤掉第几行，起始行=0
                Row row = rows.next();
                if (row.getRowNum() <= limit) {
                    continue;
                }

                Field[] fields = clazz.getDeclaredFields();
                List<Field> fieldList = new ArrayList<>();
                for (Field field : fields) {
                    //去除一些关键词
                    if (denyFields.contains(field.getName())) {
                        continue;
                    }

                    fType = field.getType();
                    if (!fType.isPrimitive()) {
                        continue;
                    }
                    if (Modifier.isStatic(fType.getModifiers())) {
                        continue;
                    }
                    if (fType == Integer.class || fType == Long.class
                            || fType == Short.class || fType == Boolean.class
                            || fType == Character.class || fType == Double.class
                            || fType == String.class || fType == Double.class) {
                        fieldList.add(field);
                    }
                }

                if (fieldList.isEmpty()) {
                    continue;
                }
                //实例化
                t = clazz.newInstance();
                int fL = fieldList.size();
                for (int j = 0; j < fL; j++) {
                    setFieldValue(t, fieldList.get(j), row.getCell(j));
                }
                list.add(t);
            }
        }
        return list;
    }

    /**
     * 数据导入
     * 用户自定义返回值导入
     *
     * @param startRow 导入起始行,-1表示读取全部，0表示送0开始
     * @param <T>
     */
    public <T> List<T> importWithCustom(int startRow, ExcelParseCell<T> parseCell)
            throws IllegalAccessException, InstantiationException, IOException {
        List<T> lists = new ArrayList<>();
        //获取多少个页面，底部
        int sheetsCount = workbook.getNumberOfSheets();
        T t = null;
        for (int i = 0; i < sheetsCount; i++) {
            //获取第几个
            Sheet sheet = workbook.getSheetAt(i);
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                // 过滤掉第几行，起始行=0
                Row row = rows.next();
                if (row.getRowNum() < startRow) {
                    continue;
                }
                //获取实例
                t = parseCell.get(row);
                lists.add(t);
            }
        }
        return lists;
    }


}
